﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data.Entity.Migrations.Model;
using System.IO;

namespace homework8
{
    class Data
    {
        public string English { get; set; }
        public string Chinese { get; set; }

        public string init { get; set; }
    }
    internal class Database
    {
        private SQLiteConnection connection;
        private bool status;
        private List<Data> data;

        public List<Data> GetData { get => data; }
        public Database() {
            status = File.Exists("./English_Exercise.db");
            connection = new SQLiteConnection("Data Source=English_Exercise.db;Version=3");
            connection.Open();
            data = new List<Data>();
        }

        public void CreateTable() {
            string command = "create table if not exists Words_Set (English varchar(20),Part_Of_Speech varchar(10),Chinese varchar(20))";
            SQLiteCommand Commond=new SQLiteCommand(command, connection);
            Commond.ExecuteNonQuery();
        }

        public void InsertData() {
            if (status)
                return;
            string command = "insert into Words_Set values('alter','v','改变'),('dispose','v','处置，解决')," +
                "('consume','v','消耗'),('split','v','劈开'),('slip','v','滑倒'),('bacteria','n','细菌')," +
                "('transform','v','转变'),('candidate','n','候选人'),('breed','n','品种'),('transmit','v','传播')," +
                "('suspicious','adj','怀疑的'),('absolute','adj','绝对的'),('shift','v','转移,转动')," +
                "('mild','adj','温暖的'),('nuisance','n','损害'),('vague','adj','模糊的'),('extraordinary','adj','非凡的')," +
                "('stimulate','v','激发'),('tide','n','潮汐'),('wax','n','蜡'),('herd','n','兽群')," +
                "('academic','adj','学术的'),('vertical','adj','垂直的'),('agent','n','代理人'),('alcohol','n','酒精')," +
                "('accomplish','v','完成,到达'),('brake','n','刹车,制动器'),('vain','n','徒劳'),('decent','adj','体面的')," +
                "('abundant','adj','丰富的,大量的'),('bachelor','n','学士'),('vacuum','n','真空'),('oral','adj','口头的')"; 
            SQLiteCommand Commond = new SQLiteCommand(command, connection);
            Commond.ExecuteNonQuery();
        }

        public void Select(string desti) { 
            string command = "select English,Chinese from Words_Set where Part_Of_Speech ="+"'"+desti+"' ORDER BY RANDOM() limit 5";
            SQLiteCommand Commond = new SQLiteCommand(command, connection);
            SQLiteDataReader reader=Commond.ExecuteReader();
            while (reader.Read())
            {
                data.Add(new Data() { Chinese = (string)reader["Chinese"], English = (string)reader["English"] ,init=""});
            }
        }

        public void reSet() {
            data.Clear();
        }
    }
}
